IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ScriptDefaultConstraintTable'))
BEGIN
	DROP PROCEDURE MD3.ScriptDefaultConstraintTable
END
GO

CREATE Procedure MD3.ScriptDefaultConstraintTable

AS  
BEGIN

/*

	Generates a SQL script that can be saved to 04DefaultConstraints, in MasterData format, that is used as the basis for all DEFAULT constraint manipulation.



*/


PRINT '/*'
PRINT '	This script holds the MasterData about our CHECK Constraints'
PRINT '	Simply modify this metadata and rerun the MD3 deployment and any DEFAULT constraint changes'
PRINT '		will be reflected in your database.  '
PRINT ''
PRINT '	To add a new DEFAULT constraint...Add a new INSERT line to the file.'
PRINT '	To modify a DEFAULT constraint...Modify the values of the EXISTING check constraint'
PRINT '	To delete a DEFAULT constraint...Set the DropConstraint flag to 1.  Do not actually delete the row.'
PRINT ''
PRINT ' This script can be automatically regenerated by running MD3.ScriptDefaultConstraintTable'
PRINT '*/'

SET NOCOUNT ON
PRINT 'SET NOCOUNT ON'
PRINT '--remove all old entries from table'
PRINT 'TRUNCATE TABLE MD3.DefaultConstraints'
PRINT ''
PRINT '--add new entries to table'

DECLARE @exec_str VARCHAR(MAX), @new_str VARCHAR(MAX), @quote VARCHAR(MAX) = '''';
SELECT @exec_str = 'INSERT INTO MD3.DefaultConstraints (TableName, SchemaName, ConstraintName, ConstraintExpression, ColumnName, DropConstraint) VALUES('

DECLARE 
	@TableName varchar(256)
	,@SchemaName varchar(256)
	,@ConstraintName varchar(256)
	,@ConstraintExpression varchar(max)
	,@ColumnName varchar(256)
	,@DropConstraint varchar(256)

DECLARE dave CURSOR FOR 
	SELECT 
		TableName, SchemaName, ConstraintName, ConstraintExpression, ColumnName, DropConstraint 
	FROM MD3.DefaultConstraints
	ORDER BY SchemaName, TableName
OPEN dave
FETCH NEXT FROM dave INTO 
	@TableName
	,@SchemaName 
	,@ConstraintName 
	,@ConstraintExpression 
	,@ColumnName
	,@DropConstraint 
WHILE (@@FETCH_STATUS = 0)
BEGIN

	SELECT @new_str = @exec_str + '''' + @TableName + ''',''' + @SchemaName + ''',''' + @ConstraintName + ''',''' + REPLACE (@ConstraintExpression,'''','''''') + ''',''' + @ColumnName + ''','
	SELECT @new_str += @DropConstraint +');'
	PRINT @new_str
	
	
	FETCH NEXT FROM dave INTO 
		@TableName
		,@SchemaName 
		,@ConstraintName 
		,@ConstraintExpression 
		,@ColumnName
		,@DropConstraint 
END;
CLOSE dave
DEALLOCATE dave


PRINT '--now building DEFAULT Constraints'
PRINT 'EXEC MD3.RunDefaultConstraints'
PRINT 'GO'
PRINT 'SET NOCOUNT OFF'

END
GO
